package com.adb.indian.rto.utility;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {

	SQLiteDatabase db;
    public DBHelper() {
            super(Const.CONTEXT, Const.DB_NAME, null,33);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    	this.db = db;
    }
 
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
    
    public void execute(String Statment) {
            db = this.getWritableDatabase();

            try {
                    db.execSQL(Statment);
            } catch (Exception e) {
                    Log.error("DataBaseHelper - ExecuteQuery", e);
                    Log.println("SQL : ", Statment);
            } finally {
                    db.close();
                    db = null;
            }
    }

    public Cursor query(String Statment) {
            Cursor cur = null;
            SQLiteDatabase db = this.getWritableDatabase();

            try {
                    cur = db.rawQuery(Statment, null);
                    cur.moveToPosition(-1);
            } catch (Exception e) {
                    Log.error("DataBaseHelper - ExecuteCursor", e);
            } finally {

                    db.close();
                    db = null;
            }

            return cur;
    }

    public static String getDBStr(String str) {

            str = str != null ? str.replaceAll("\'", "\'\'") : null;
            return str;

    }
    
    /**
     * THIS IS WHERE WE NEED TO PASS UPGRADE LEVEL OF APPLICATION DATABASE
     * @param level
     */
    
    public void upgrade(int level) {
            switch (level) {
            case 0:
                  //  doUpdate0();
                    break;
            case 1:
                    // doUpdate1();
            	    break;
            case 2:
                    // doUpdate2();
            		break;
            case 3:
                    // doUpdate3();
            		break;

            }
    }

    private void doUpdate0() {
    		
    		//GROUP MASTER 
            this.execute("CREATE TABLE Group_Master(GroupId INTEGER PRIMARY KEY, Name TEXT, Desc Text)");
            
            //CATEGORY MASTER
            this.execute("CREATE TABLE Category_Master(CatId INTEGER PRIMARY KEY, Name TEXT)");
            
            //ITEM MASTER
            this.execute("CREATE TABLE Item_Master(ItemId INTEGER PRIMARY KEY, Name TEXT, Category_Id INTEGER, "
				+ "FOREIGN KEY(Category_Id) REFERENCES Category_Master(CatId))");
            
            //EXPERIMENT MASTER
            this.execute("CREATE TABLE Experiment_Master(ExpId INTEGER PRIMARY KEY, SubjectID TEXT, ExpDate INTEGER, SeessionNo TEXT, CongnitiveDemand TEXT, PhysicalDemand Text, Notes TEXT, ExpStart INTEGER, ExpFinish INTEGER, Status INTEGER, FileName TEXT)");
             
            // EXPERIMENT GROUP REL
            this.execute("CREATE TABLE Experiment_Group_Rel(ExpGroupId INTEGER PRIMARY KEY, Expriment_Id INTEGER, Group_Id INTEGER, OrderNo INTEGER," +
            		"FOREIGN KEY(Expriment_Id) REFERENCES Experiment_Master(ExpId), FOREIGN KEY(Group_Id) REFERENCES Group_Master(GroupId))");
            
            //EXPERIMENT GROUP TRIAL REL
            this.execute("CREATE TABLE Experiment_Group_Trial_Rel(ExpTrialId INTEGER PRIMARY KEY, Expriment_Id INTEGER, ExpGroup_Id INTEGER, TrialNo INTEGER, Status INTEGER, TrialStart INTEGER, TrialFinish INTEGER, " +
        				"FOREIGN KEY(Expriment_Id) REFERENCES Experiment_Group_Rel(Expriment_Id), FOREIGN KEY(ExpGroup_Id) REFERENCES Experiment_Group_Rel(ExpGroupId))");
             
            //TRIAL ITEM REL
            this.execute("CREATE TABLE Experiment_Group_Trial_Item_Rel(TrialId INTEGER PRIMARY KEY, ExpGroupTrial_Id INTEGER, Trial_No INTEGER, Category_Id INTEGER, Item_Id INTEGER, " +
            			"FOREIGN KEY(ExpGroupTrial_Id) REFERENCES Experiment_Group_Trial_Rel(ExpGroup_Id), FOREIGN KEY(Trial_No) REFERENCES Experiment_Group_Trial_Rel(TrialNo), FOREIGN KEY(Item_Id) REFERENCES Item_Master(ItemId), FOREIGN KEY(Category_Id) REFERENCES Category_Master(CatId))");
            
            //TRIAL ITEM SELECTED REL
            this.execute("CREATE TABLE Experiment_User_Selected_Item_Rel(TrialId INTEGER PRIMARY KEY, ExpGroupTrial_Id INTEGER, Trial_No INTEGER, Category_Id INTEGER, Item_Id INTEGER, " +
            			"FOREIGN KEY(ExpGroupTrial_Id) REFERENCES Experiment_Group_Trial_Rel(ExpGroup_Id), FOREIGN KEY(Item_Id) REFERENCES Item_Master(ItemId), FOREIGN KEY(Category_Id) REFERENCES Category_Master(CatId))");  
            
            
            
            // START MINIMAL DEFAUL DATA INSERT INTO TABLE 
            insertGroupMaster();
            
            insertCategoryMaster();
            
            insertItemMaster();
            
    }
    
    
    public SQLiteDatabase getConnection() {
            SQLiteDatabase dbCon = this.getWritableDatabase();
            return dbCon;
    }       
    
    
    private void insertGroupMaster(){
    	
    	this.execute("INSERT INTO Group_Master(Name, Desc) Values ('LS','Low cognitive demand with Sliding button')");
    	this.execute("INSERT INTO Group_Master(Name, Desc) Values ('LT','Low cognitive demand with Take-off button')");
    	this.execute("INSERT INTO Group_Master(Name, Desc) Values ('HS','High cognitive demand with Sliding button')");
    	this.execute("INSERT INTO Group_Master(Name, Desc) Values ('HT','High cognitive demand with Take-off button')");
    }
    
    private void insertCategoryMaster(){
    	
		this.execute("INSERT INTO Category_Master(Name)Values('Dairy')");
		this.execute("INSERT INTO Category_Master(Name)Values('Fruit')");
		this.execute("INSERT INTO Category_Master(Name)Values('Meat')");
		this.execute("INSERT INTO Category_Master(Name)Values('Vegetable')");
    }
    
    private void insertItemMaster(){
    	
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Butter',1)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Cheddar Cheese',1)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Cream Cheese',1)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Ice Cream',1)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Low Fat Milk',1)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Low Fat  Yogurt',1)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Sour  Cream',1)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Swiss Cheese',1)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Whole Milk',1)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Yogurt',1)");
    	
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Apple',2)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Banana',2)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Cherry',2)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Blueberry',2)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Grape',2)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Kiwi fruit',2)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Mango',2)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Orange',2)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Peach',2)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Pear',2)");
    	
    	
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Chicken Breast',3)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Chicken Thigh',3)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Fish',3)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Ground Beef',3)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Ham',3)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Lamb',3)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Lobster',3)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Pork Chop',3)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Shrimp',3)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Steak',3)");
    	
    	
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Artichoke',4)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Broccoli',4)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Cabbage',4)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Celery',4)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Collard Greens',4)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Lettuce',4)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Onion',4)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Potato',4)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Spinach',4)");
    	this.execute("INSERT INTO Item_Master(Name, Category_Id)Values('Turnip',4)");
    	
    	
    }

}
